﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Linq;
using BE;
namespace DAL
{
	public class EdgeDA
	{
		//Define Parameters to use in objectCommand objects
		private const string ParamId = "@id";
		private const string ParamName = "@name";
		private const string ParamL1 = "@l1";
		private const string ParamL2 = "@l2";
		private const string ParamDir = "@dir";
		private const string ParamStraight = "@straight";
		private const string ParamWeight = "@weight";

		//define name of Database values
		private const string Table = "Paths";
		private const string ColId = "ID";
		private const string ColName = "Name";
		private const string ColL1 = "L1";
		private const string ColL2 = "L2";
		private const string ColDir = "Direction";
		private const string ColStraight = "Straight";
		private const string ColWeight = "Weight";
		private const string space = ", ";

		private SqlConnection con;

		public EdgeDA()
		{
			SqlCommands cmd = new SqlCommands();
			con = cmd.GetConnection();
		}

		private Edge extractEdge( SqlDataReader reader )
		{
			return new Edge( reader.GetInt32( reader.GetOrdinal( ColId ) ),
				reader.GetString( reader.GetOrdinal( ColName ) ),
                reader.GetString(reader.GetOrdinal( ColDir )),
				reader.GetInt32( reader.GetOrdinal( ColL1 ) ),
				reader.GetInt32( reader.GetOrdinal( ColL2 ) ),
				reader.GetBoolean( reader.GetOrdinal( ColStraight ) ),
				reader.GetInt32( reader.GetOrdinal( ColWeight ) ) );

		}

		private void setCommand( SqlCommand cmd, Edge edge )
		{
			cmd.Parameters.AddWithValue( ParamName, edge.Name );
			cmd.Parameters.AddWithValue( ParamL1, edge.L1 );
			cmd.Parameters.AddWithValue( ParamL2, edge.L2 );
			cmd.Parameters.AddWithValue( ParamDir, edge.Direction );
			cmd.Parameters.AddWithValue( ParamStraight, edge.Straight );
			cmd.Parameters.AddWithValue( ParamWeight, edge.Weight );
		}

		public int AddEdge( Edge pEdge )
		{
			string insertString = "INSERT INTO " + Table + "(" + ColName + space + ColL1 + space + ColL2 + space + ColDir + space + ColStraight + space + ColWeight + ") VALUES(" + ParamName + space + ParamL1 + space + ParamL2 + space + ParamDir + space + ParamStraight + space + ParamWeight + ")";
			SqlCommand cmd = new SqlCommand( insertString, con );
			setCommand( cmd, pEdge );
			try
			{
				con.Open();
				return cmd.ExecuteNonQuery();
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public Edge GetEdge( int id )
		{
			string sqlString = "SELECT * FROM " + Table + " WHERE " + ColId + " = " + id;
			SqlCommand cmd = new SqlCommand( sqlString, con );
			SqlDataReader reader;
			try
			{
				con.Open();
				reader = cmd.ExecuteReader();
				reader.Read();
				Edge e = extractEdge( reader );
				reader.Close();
				return e;
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public List<Edge> GetAllEdges()
		{
			string sqlString = "SELECT * FROM " + Table;
			SqlCommand cmd = new SqlCommand( sqlString, con );
			SqlDataReader reader;
			try
			{
				con.Open();
				reader = cmd.ExecuteReader();
				List<Edge> list = new List<Edge>();
				while ( reader.Read() )
					list.Add( extractEdge( reader ) );
				reader.Close();
				return list;
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public int EditEdge( Edge edge )
		{
			string sqlString = "UPDATE "+Table+" set ";
			
			sqlString += ColName + "=" + ParamName+space;
			sqlString += ColL1 + "=" + ParamL1 + space;
			sqlString += ColL2 + "=" + ParamL2 + space;
			sqlString += ColDir + "=" + ParamDir + space;
			sqlString += ColStraight + "=" + ParamStraight + space;
			sqlString += ColWeight + "=" + ParamWeight;
			sqlString += " Where " + ColId +"="+ edge.ID;

			SqlCommand cmd = new SqlCommand( sqlString, con );
			setCommand( cmd, edge );
			try
			{
				con.Open();
				return cmd.ExecuteNonQuery();
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public int DeleteEdge( int id )
		{
			string sqlString = "DELETE FROM " + Table + " WHERE "+ColId+"="+id;

			
			SqlCommand cmd = new SqlCommand( sqlString, con );

			try
			{
				con.Open();
				return cmd.ExecuteNonQuery();
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}
	}
}
